package com.example.huixue.mapper;

import com.example.huixue.entity.Rating;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface RatingMapper {
    
    /**
     * 插入新评分
     */
    @Insert("INSERT INTO ratings (user_id, resource_id, score, created_at, updated_at) " +
            "VALUES (#{userId}, #{resourceId}, #{score}, #{createdAt}, #{updatedAt})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(Rating rating);
    
    /**
     * 根据ID查找评分
     */
    @Select("SELECT r.*, u.username, res.title as resource_title " +
            "FROM ratings r " +
            "LEFT JOIN users u ON r.user_id = u.user_id " +
            "LEFT JOIN resources res ON r.resource_id = res.resource_id " +
            "WHERE r.id = #{id}")
    Rating findById(Long id);
    
    /**
     * 更新评分
     */
    @Update("UPDATE ratings SET score = #{score}, updated_at = #{updatedAt} WHERE id = #{id}")
    int update(Rating rating);
    
    /**
     * 删除评分
     */
    @Delete("DELETE FROM ratings WHERE id = #{id}")
    int deleteById(Long id);
    
    /**
     * 根据用户ID和资源ID查找评分
     */
    @Select("SELECT r.*, u.username, res.title as resource_title " +
            "FROM ratings r " +
            "LEFT JOIN users u ON r.user_id = u.user_id " +
            "LEFT JOIN resources res ON r.resource_id = res.resource_id " +
            "WHERE r.user_id = #{userId} AND r.resource_id = #{resourceId}")
    Rating findByUserAndResource(@Param("userId") Long userId, @Param("resourceId") Long resourceId);
    
    /**
     * 获取资源的所有评分
     */
    @Select("SELECT r.*, u.username, res.title as resource_title " +
            "FROM ratings r " +
            "LEFT JOIN users u ON r.user_id = u.user_id " +
            "LEFT JOIN resources res ON r.resource_id = res.resource_id " +
            "WHERE r.resource_id = #{resourceId} " +
            "ORDER BY r.created_at DESC LIMIT #{offset}, #{limit}")
    List<Rating> findByResourceWithPagination(@Param("resourceId") Long resourceId, 
                                               @Param("offset") int offset, @Param("limit") int limit);
    
    /**
     * 获取资源评分总数
     */
    @Select("SELECT COUNT(*) FROM ratings WHERE resource_id = #{resourceId}")
    long countByResource(Long resourceId);
    
    /**
     * 获取资源平均评分
     */
    @Select("SELECT AVG(score) FROM ratings WHERE resource_id = #{resourceId}")
    Double getAverageScore(Long resourceId);
    
    /**
     * 获取资源评分分布
     */
    @Select("SELECT score, COUNT(*) as count " +
            "FROM ratings " +
            "WHERE resource_id = #{resourceId} " +
            "GROUP BY score " +
            "ORDER BY score DESC")
    List<Map<String, Object>> getScoreDistribution(Long resourceId);
    
    /**
     * 获取用户的所有评分
     */
    @Select("SELECT r.*, u.username, res.title as resource_title " +
            "FROM ratings r " +
            "LEFT JOIN users u ON r.user_id = u.user_id " +
            "LEFT JOIN resources res ON r.resource_id = res.resource_id " +
            "WHERE r.user_id = #{userId} " +
            "ORDER BY r.created_at DESC LIMIT #{offset}, #{limit}")
    List<Rating> findByUserWithPagination(@Param("userId") Long userId, 
                                           @Param("offset") int offset, @Param("limit") int limit);
    
    /**
     * 获取用户评分总数
     */
    @Select("SELECT COUNT(*) FROM ratings WHERE user_id = #{userId}")
    long countByUser(Long userId);
    
    /**
     * 获取最新评分
     */
    @Select("SELECT r.*, u.username, res.title as resource_title " +
            "FROM ratings r " +
            "LEFT JOIN users u ON r.user_id = u.user_id " +
            "LEFT JOIN resources res ON r.resource_id = res.resource_id " +
            "ORDER BY r.created_at DESC LIMIT #{limit}")
    List<Rating> findLatestRatings(@Param("limit") int limit);
    
    /**
     * 获取评分总数
     */
    @Select("SELECT COUNT(*) FROM ratings")
    long countAll();
    
    /**
     * 检查用户是否已对资源评分
     */
    @Select("SELECT COUNT(*) > 0 FROM ratings WHERE user_id = #{userId} AND resource_id = #{resourceId}")
    boolean existsByUserAndResource(@Param("userId") Long userId, @Param("resourceId") Long resourceId);
    
    /**
     * 删除资源的所有评分记录
     */
    @Delete("DELETE FROM ratings WHERE resource_id = #{resourceId}")
    int deleteByResource(Long resourceId);
} 